package com.example.yls.demoa;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

import java.io.File;

public class MainActivity extends AppCompatActivity {
    private Button btnCreateDB;
    private Button btnCreateTable;
    private Button btnInsert;
    private EditText edtNo;
    private EditText edtName;
    private EditText edtAge;
    private Button btnRead;
    private TextView txtData;
    private SQLiteDatabase db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        findViews();

        btnCreateDB.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                //创建数据库
                createOrOpenDB();
            }
        });

        btnCreateTable.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                //创建学生表
                createTable();
            }
        });
        btnInsert.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                //插入数据
                String name=edtName.getText().toString();
                String no=edtNo.getText().toString();
                int age=Integer.valueOf(edtAge.getText().toString());
                insertStudent(no,name,age);
            }
        });
        btnRead.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                //从数据库读取数据并且显示
                readDataFromDB();
            }
        });
    }

    private void readDataFromDB() {
        if(db==null){
            createOrOpenDB();
        }
        //列名为null默认读取全部
        Cursor cursor=db.query("student",null,null,
                null,null, null,null);
        StringBuffer buffer=new StringBuffer();
        //isAfterLast:在最后一个的后面
        //查询出来的cursor的初始位置是指向第一条记录的前一个位置的
        //cursor.moveToFirst（）指向查询结果的第一个位置。
        //一般通过判断cursor.moveToFirst()的值为true或false来确定查询结果是否为空。
        //cursor.moveToNext()是用来做循环的
        for(cursor.moveToFirst();!cursor.isAfterLast();cursor.moveToNext()){
            String no=cursor.getString(0);
            String name=cursor.getString(1);
            int age=cursor.getInt(2);

            buffer.append(no).append("  ").append(name).append("  ").append(age).append("\n");
        }
        txtData.setText(buffer);
    }

    private void findViews() {
        btnCreateDB=findViewById(R.id.btn_createDB);
        btnCreateTable=findViewById(R.id.btn_createTable);
        btnInsert=findViewById(R.id.btn_insert);
        edtNo=findViewById(R.id.edtNo);
        edtName=findViewById(R.id.edtName);
        edtAge=findViewById(R.id.edtAge);
        btnRead=findViewById(R.id.btn_read);
        txtData=findViewById(R.id.txt_data);
    }

    private void insertStudent(String no,String name,int age) {
        if(db==null){
            createOrOpenDB();
        }
        //第一种 组装我们的sql语句
        //String sql="insert into student values('17001','lisa',18)";
        /*String sql="insert into student values ('" +no+"','"+name+"',"+age+")";
        db.execSQL(sql);*/

        //第二种 使用insert接口
        ContentValues values=new ContentValues();
        values.put("stuno",no);
        values.put("name",name);
        values.put("age",age);
        db.insert("student",null,values);
    }

    private void createTable() {
        if(db==null){
            createOrOpenDB();
        }
        //sql语句
        String sql="create table if not exists student (stuno varchar(20)," +
                "name varchar(20),age int)";
        db.execSQL(sql); //用来执行SQL代码
    }

    private void createOrOpenDB() {
        //path:目录
        String path=getFilesDir().getAbsolutePath()+ File.separator+"stu.db";
        Log.e("MainActivity","path="+path);

        db=SQLiteDatabase.openOrCreateDatabase(path,null);
    }
}
